NOT
We often know what data we're looking for. However, sometimes we know what data we're NOT looking for.
This operator is extremely useful as it allows us to exclude items from our qeury instead of naming everything we want. NOT essentially returns the opposite of whatever criteria you specify and can be used in conjunction with other logical operators such as IN, LIKE, and BETWEEN. Let's look at some examples.
SQL Syntax: Using NOT
Example:
Customers Table
First_Name | Last_Name | Cust_State
-----------|-----------|------------
Alice | Johnson | AZ
Bob | Smith | AK
Raymond | Barone | NY
Michael | Scott | PA
John | Doe | FL
Bob | Jacobson | OK
NOT
Prompt
Find the first and last names of all customers who do not live in Arizona.
Query
SELECT *
FROM Customers
WHERE Cust_State NOT = 'AZ'
Result
First_Name | Last_Name | Cust_State
-----------|-----------|------------
Bob | Smith | AK
Raymond | Barone | NY
Michael | Scott | PA
John | Doe | FL
Bob | Jacobson | OK
This query resulted in everyone except for Alice Johnson to return.
NOT IN
Prompt
Find the first and last names of all customers who do not live in Arizona, New York, and Oklahoma
Query
SELECT *
FROM Customers
WHERE Cust_State NOT IN ('AZ','FL','OK')
Result
First_Name | Last_Name | Cust_State
-----------|-----------|------------
Bob | Smith | AK
Raymond | Barone | NY
Michael | Scott | PA
This query excluded Alice Johnson, Raymond Barone, and Bob Jacobson from the result set.
NOT LIKE
Prompt
Find the first and last names of all customers who's first name does not start with the letter B.
Query
SELECT *
FROM Customers
WHERE First_Name NOT LIKE 'B%'
Result
First_Name | Last_Name | Cust_State
-----------|-----------|------------
Raymond | Barone | NY
Michael | Scott | PA
John | Doe | FL
This query resulted in everyone except for the Bobs to return.
Wrapping Up
The logical operator NOT is a great way to quickly sift through the data. Using it ensures that unwanted data is excluded from the result set.